package com.kuang.dao.provider;

import com.kuang.dao.BaseDao;
import com.kuang.pojo.Provider;
import com.kuang.pojo.Role;
import com.mysql.jdbc.StringUtils;
import com.sun.org.apache.xpath.internal.objects.XObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProviderDaoImpl implements ProviderDao{


    public int add(Connection connection, Provider provider) throws Exception {
        PreparedStatement pstm = null;
        int flag = 0;
        if (connection!=null){
            String sql = "insert into smbms_provider (proCode, proName, proDesc, proContact, proPhone, proAddress, proFax, createdBy, creationDate)\n" +
                    "values(?,?,?,?,?,?,?,?,?)";
            Object[] params = {provider.getProCode(),provider.getProName(),provider.getProDesc(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(),provider.getProFax(),provider.getCreatedBy(),provider.getCreationDate()};
            flag = BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return flag;
    }

    public List<Provider> getProviderList(Connection connection) throws Exception {
        PreparedStatement pstm = null;
        ResultSet resultSet = null;
        ArrayList<Provider> provider = new ArrayList<Provider>();

        if (connection!=null){
            String sql = "select * from smbms_provider";
            Object[] params = {};
            resultSet = BaseDao.execute(connection,pstm,resultSet,sql,params);

            while (resultSet.next()){
                Provider _provider = new Provider();
                _provider.setId(resultSet.getInt("id"));
                _provider.setProCode(resultSet.getString("proCode"));
                _provider.setProName(resultSet.getString("proName"));
                provider.add(_provider);
            }
            BaseDao.closeResource(null,pstm,resultSet);
        }
        return provider;
    }

    //根据用户输入的值，查询供应商的列表
    public List<Provider> getProviderLists(Connection connection, String providerName, String providerCode) throws Exception {
        PreparedStatement pstm = null;
        ResultSet resultSet = null;
        List<Provider> providerList = new ArrayList<Provider>();

        if (connection!=null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from smbms_provider p where p.proName like '%%'");
            List<Object> list = new ArrayList<Object>();//存放参数

            if (!StringUtils.isNullOrEmpty(providerName)){
                sql.append(" and p.proName like ? ");
                list.add("%"+ providerName +"%");
            }
            if (!StringUtils.isNullOrEmpty(providerCode)){
                sql.append(" and p.proCode like ?");
                list.add("%"+ providerCode +"%");
            }

            Object[] params = list.toArray();

            System.out.println("sqlProvider---->"+sql.toString());

            resultSet = BaseDao.execute(connection,pstm,resultSet,sql.toString(),params);
            while (resultSet.next()){
                Provider _p = new Provider();
                _p.setId(resultSet.getInt("id"));
                _p.setProCode(resultSet.getString("proCode"));
                _p.setProName(resultSet.getString("proName"));
                _p.setProDesc(resultSet.getString("proDesc"));
                _p.setProContact(resultSet.getString("proContact"));
                _p.setProPhone(resultSet.getString("proPhone"));
                _p.setProAddress(resultSet.getString("proAddress"));
                _p.setProFax(resultSet.getString("proFax"));
                _p.setCreationDate(resultSet.getDate("creationDate"));
                providerList.add(_p);
            }
            BaseDao.closeResource(null,pstm,resultSet);
        }
        return providerList;
    }

    public int deleteProviderById(Connection connection, String delId) throws Exception {
        PreparedStatement pstm = null;
        int flag = 0;
        if (connection!= null){
            String sql = "delete from smbms_provider where id = ?";
            Object[] params = {delId};
            flag = BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(connection,pstm,null);
        }
        return flag;
    }
}
